﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using Showme.Data.Utils;
using Showme.Data.Objects;
using Showme.Utils;

namespace Showme.Data
{
    public class DataAccessLayer
    {
        /* Manage Users */
        public DataRow getUser(string username, string password)
        {
            var dt = AdoHelper.ExecuteDataTable(string.Format("SELECT fld_username, fld_password, fld_userID, fld_userType FROM tbl_users WHERE tbl_users.fld_username = '{0}' AND tbl_users.fld_password = '{1}'", username.getSqlSafeString(), password.getSqlSafeString()));
            if (dt.Rows.Count > 0)
                return dt.Rows[0];
            else
                return null;
        }

        public DataRow getUser(string userid)
        {
            var dt = AdoHelper.ExecuteDataTable(string.Format("SELECT * FROM tbl_users WHERE fld_userID='{0}'", userid));
            if (dt.Rows.Count > 0)
                return dt.Rows[0];
            else
                return null;
        }

        public DataTable getAllUsers(UserType userType = UserType.All)
        {
            string query = "SELECT tbl_users.fld_userID, tbl_users.fld_username, tbl_userTypes.fld_typeTitle FROM tbl_userTypes INNER JOIN tbl_users ON tbl_userTypes.fld_typeID = tbl_users.fld_userType";
            if (userType == UserType.All)
                return AdoHelper.ExecuteDataTable(query);
            else
                return AdoHelper.ExecuteDataTable(query + " WHERE tbl_userTypes.fld_typeID = " + (int)userType);
        }

        public void deleteUser(string userID)
        {
            AdoHelper.ExecuteNonQuery(string.Format("DELETE FROM tbl_users WHERE fld_userID='{0}'", userID));
        }

        /*public void saveMedia(string name, string genre, string link)
        {
            var userRow = getUser(userid);
            string query;

            if (userRow != null)
            {
                // update user
                query = string.Format("UPDATE tbl_users SET fld_username='{0}', fld_password='{1}', fld_userType={2} WHERE fld_userID='{3}'", username.getSqlSafeString(), password.getSqlSafeString(), userType, userid.getSqlSafeString());
            }
            
            // insert media
            query = string.Format("INSERT INTO tbl_media (ID, Name, Genre, Link) VALUES ('{0}','{1}','{2}',{3})", userid.getSqlSafeString(), username.getSqlSafeString(), password.getSqlSafeString(), userType);
            

            AdoHelper.ExecuteNonQuery(query);
        }*/

        public DataTable getUserTypes()
        {
            return AdoHelper.ExecuteDataTable("SELECT * FROM tbl_userTypes");
        }

        public DataTable getStudentCoursesGrades(string studentID)
        {
            return AdoHelper.ExecuteDataTable(string.Format("SELECT tbl_courses.fld_courseID, tbl_courses.fld_courseName, Round(Avg(tbl_submissions.fld_grade),2) AS Grade FROM (tbl_courses INNER JOIN (tbl_assignments INNER JOIN tbl_submissions ON tbl_assignments.fld_assignmentID = tbl_submissions.fld_assignmentID) ON tbl_courses.fld_courseID = tbl_assignments.fld_courseID) INNER JOIN tbl_usersCourses ON tbl_courses.fld_courseID = tbl_usersCourses.fld_courseID WHERE tbl_usersCourses.fld_userID = '{0}' GROUP BY tbl_courses.fld_courseID, tbl_courses.fld_courseName", studentID));
        }

        internal DataTable GetAllMedia()
        {
            return AdoHelper.ExecuteDataTable("SELECT Name, Link FROM tbl_media");
        }
    }
}
